﻿/* [GestionePolveri].[GP].[FamiglieColoriXPolveri] */
WITH cteColoriAbbinati(IDPolvere, IDFamiglia) AS 
(
	select cc.idpolvere, ac.idFamiglia
	FROM  gp.composizionecolori as cc 
	inner join gp.anagcolori as ac on cc.idcolore = ac.idcolore
	inner join gp.anagpolveri as ap on cc.idpolvere = ap.idpolvere 
)
insert into [GP].[FamiglieColoriXPolveri]
select  cte.IDPolvere
        , Stuff(( select distinct ',' + cast(Sub.IDFamiglia as varchar) AS [text()] from cteColoriAbbinati as Sub where Sub.IDPolvere = AP.IdPolvere for xml path('')), 1, 1, '') as 'Famiglie'
        , null
from cteColoriAbbinati as cte
inner join GP.AnagPolveri as AP on cte.IDPolvere = AP.IDPolvere
group by cte.idpolvere, AP.IDPolvere

